/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.gob.pnp.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import pe.gob.pnp.beans.DependenciaBean;
import pe.gob.pnp.factories.ConnectionPool;

/**
 *
 * @author gcarlos
 */
public class GestionDependencia {

    public List<DependenciaBean> obtenerDependenciaExterna(String dependencia) throws SQLException {
        List<DependenciaBean> lstDependencia = new ArrayList<DependenciaBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT NDEPE_IDENTIFICADOR, CDEPE_NOMBRE ";
            sql += " FROM DIRPEN.GENMA_DEPENDENCIA";
            sql += " WHERE CDEPE_NOMBRE NOT LIKE '%DIRPEN%'";
            sql += " AND CDEPE_EXTERNA = 'S'";
            sql += " AND NDEPE_NIVEL =1";
            sql += " AND BDEPE_ELIMINADO = 0";
            sql += " AND BDEPE_ACTIVO=1";
            sql += " AND NTIDE_CODIGO != 1";
            sql += " AND CDEPE_NOMBRE LIKE ? ";
            sql += " ORDER BY CDEPE_NOMBRE";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + dependencia.toUpperCase() + "%");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                DependenciaBean obj = new DependenciaBean();
                obj.setNdepeIdentificador(rs.getInt("NDEPE_IDENTIFICADOR"));
                obj.setCdepeNombre(rs.getString("CDEPE_NOMBRE"));
                lstDependencia.add(obj);
            }
            rs.close();
            ps.close();
        } catch (Exception err) {
            err.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstDependencia;
    }
    
    
    
    public List<DependenciaBean> obtenerDependenciaNiveles(int padre) throws SQLException {
        List<DependenciaBean> lstDependencia = new ArrayList<DependenciaBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT NDEPE_IDENTIFICADOR, CDEPE_NOMBRE, CDEPE_SIGLAS,";
            sql += " BDEPE_ACTIVO, NDEPE_AREAIDENTIFICADOR,";
            sql += " NTIDE_CODIGO, NDEPE_NIVEL,CDEPE_EXTERNA";
            sql += " FROM DIRPEN.GENMA_DEPENDENCIA";
            if (padre == 0){
                sql += " WHERE NDEPE_IDENTIFICADOR = NDEPE_AREAIDENTIFICADOR";
            }else{
                sql += " WHERE NDEPE_AREAIDENTIFICADOR = ? ";
            }
            sql += " AND BDEPE_ELIMINADO = 0";
            sql += " AND BDEPE_ACTIVO =1 ";
            sql += " ORDER BY CDEPE_NOMBRE ";
            PreparedStatement ps = conn.prepareStatement(sql);
            if (padre > 0){
                ps.setInt(1, padre);
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                DependenciaBean obj = new DependenciaBean();
                obj.setNdepeIdentificador(rs.getInt("NDEPE_IDENTIFICADOR"));
                obj.setCdepeNombre(rs.getString("CDEPE_NOMBRE"));
                obj.setCdepeSiglas(rs.getString("CDEPE_SIGLAS"));
                obj.setBdepeActivo(rs.getInt("BDEPE_ACTIVO"));
                obj.setNdepeAreaIdentificador(rs.getInt("NDEPE_AREAIDENTIFICADOR"));
                obj.setNtideCodigo(rs.getInt("NTIDE_CODIGO"));
                obj.setNdepeNivel(rs.getInt("NDEPE_NIVEL"));
                obj.setCdepeExterna(rs.getString("CDEPE_EXTERNA"));
                lstDependencia.add(obj);
            }
            rs.close();
            ps.close();
        } catch (Exception err) {
            err.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstDependencia;
    }
}
